﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XRepair.Models;
using RepositoryUtil.SQL;
using XRepair.Conventions.Enum;

namespace MvcXRepairCustomer.Repository.SQL
{
    public class OrderDAO : BaseDAO
    {
        public IEnumerable<Order> GetAllActiveOrders()
        {
            string spName = "GetAllActiveOrders";

            DataTable resultTab = null;
            List<Order> orderList = null;

            using (var conn = XRepairConn)
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;
                resultTab = command.ExecuteDataTable();
            }
            if (resultTab != null && resultTab.Rows.Count > 0)
            {
                orderList = this.ConvertDataTableToOrderList(resultTab);
            }

            return orderList;
        }

        public bool DeactivateOrderByID(int orderId)
        {
            string spName = "DeactivateOrderByID";

            object Rowcount = null;

            using (var conn = XRepairConn)
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("ID", orderId);
                command.AddOutParameter("Rowcount", -1);
                command.ExecuteNonQuery();

                Rowcount = command.GetParameterValue("Rowcount");
            }

            long RowcountValue;

            if (long.TryParse(Rowcount.ToString(), out RowcountValue) && RowcountValue != -1)
            {
                return true;
            }

            return false;
        }

        public bool AddNewOrder(Order order)
        {
            string spName = "AddNewOrder";
            object newIdentity = null;

            using (var conn = XRepairConn)
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("CustomerID", order.CustomerID);
                command.AddInParameter("CreatedAt", order.CreatedAt);
                command.AddInParameter("CreatedBy", order.CreatedBy);
                command.AddInParameter("CustomerDescriptionList", order.CustomerDescriptionList);
                command.AddInParameter("SymptomIDList", order.SymptomIDList);
                command.AddInParameter("ExtraFee", order.ExtraFee);
                command.AddInParameter("ExtraFeeNote", order.ExtraFeeNote);
                command.AddInParameter("BillingAddress", order.BillingAddress);
                command.AddInParameter("ShippingAddress", order.ShippingAddress);
                command.AddInParameter("CityID", order.CityID);
                command.AddInParameter("StateID", order.StateID);
                command.AddInParameter("ZipCode", order.ZipCode);

                command.AddOutParameter("NewIdentity", -1);
                command.ExecuteNonQuery();

                newIdentity = command.GetParameterValue("NewIdentity");
            }

            long newIdentityValue;

            if (long.TryParse(newIdentity.ToString(), out newIdentityValue) && newIdentityValue != -1)
            {
                return true;
            }

            return false;
        }

        public bool UpdateOrder(Order order)
        {
            string spName = "UpdateOrder";
            object Rowcount = null;

            using (var conn = XRepairConn)
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("CustomerID", order.CustomerID);
                command.AddInParameter("CustomerDescription", order.CustomerDescription);
                command.AddInParameter("ServiceID", order.ServiceID);
                command.AddInParameter("TechnicianID", order.TechnicianID);
                command.AddInParameter("AcceptedAt", order.AcceptedAt);
                command.AddInParameter("TechnicianDescription", order.TechnicianDescription);
                command.AddInParameter("DueDate", order.DueDate);
                command.AddInParameter("LastUpdatedAt", order.LastUpdatedAt);
                command.AddInParameter("LastUpdatedBy", order.LastUpdatedBy);
                command.AddInParameter("StatusID", order.StatusID);
                command.AddInParameter("ExtraFee", order.ExtraFee);
                command.AddInParameter("ExtraFeeNote", order.ExtraFeeNote);
                command.AddInParameter("BillingAddress", order.BillingAddress);
                command.AddInParameter("ShippingAddress", order.ShippingAddress);

                command.AddOutParameter("Rowcount", -1);
                command.ExecuteNonQuery();

                Rowcount = command.GetParameterValue("Rowcount");
            }

            long RowcountValue;

            if (long.TryParse(Rowcount.ToString(), out RowcountValue) && RowcountValue != -1)
            {
                return true;
            }

            return false;
        }

        public string[] GetPendingOrderByUsername(string username)
        {
            string spName = "GetPendingOrderByUsername";

            DataTable resultTab = null;
            string[] orderList = null;

            using (var conn = XRepairConn)
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("Username", username);

                resultTab = command.ExecuteDataTable();
            }

            if (resultTab != null && resultTab.Rows.Count > 0)
            {
                orderList = this.ConvertDataTableToPendingOrderInfo(resultTab);
            }

            return orderList;
        }

        public bool AddPendingOrder(string username, string[] orderInfo)
        {
            string spName = "AddPendingOrderByUsername";
            object newIdentity = null;

            using (var conn = XRepairConn)
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("Username", username);
                command.AddInParameter("Address", orderInfo[0]);
                command.AddInParameter("Device", orderInfo[1]);
                command.AddInParameter("SymptomInfo", orderInfo[2]);

                command.AddOutParameter("NewIdentity", -1);
                command.ExecuteNonQuery();

                newIdentity = command.GetParameterValue("NewIdentity");
            }

            long newIdentityValue;

            if (long.TryParse(newIdentity.ToString(), out newIdentityValue) && newIdentityValue != -1)
            {
                return true;
            }

            return false;
        }

        private string[] ConvertDataTableToPendingOrderInfo(DataTable data)
        {
            if (data != null && data.Rows.Count > 0)
            {
                DataRow row = data.Rows[0];

                string address = SqlUtil<string>.GetValue(row, "Address");
                string device = SqlUtil<string>.GetValue(row, "Device");
                string selectedSymptoms = SqlUtil<string>.GetValue(row, "SymptomInfo");

                return new string[] { address, device, selectedSymptoms };
            }

            return new string[3];
        }

        private List<Order> ConvertDataTableToOrderList(DataTable data)
        {
            List<Order> orders = null;

            if (data != null)
            {
                orders = new List<Order>();

                foreach (DataRow dr in data.Rows)
                {
                    var serviceModel = new Order();

                    serviceModel.ID = SqlUtil<int>.GetValue(dr, "ID");
                    serviceModel.CustomerID = SqlUtil<int>.GetValue(dr, "CustomerID");
                    serviceModel.CreatedAt = SqlUtil<DateTime>.GetValue(dr, "CreatedAt");
                    serviceModel.CustomerDescription = SqlUtil<string>.GetValue(dr, "CustomerDescription");
                    serviceModel.ServiceID = SqlUtil<int>.GetValue(dr, "ServiceID");
                    serviceModel.TechnicianID = SqlUtil<int>.GetValue(dr, "TechnicianID");
                    serviceModel.AcceptedAt = SqlUtil<DateTime>.GetValue(dr, "AcceptedAt");
                    serviceModel.TechnicianDescription = SqlUtil<string>.GetValue(dr, "TechnicianDescription");
                    serviceModel.DueDate = SqlUtil<DateTime>.GetValue(dr, "DueDate");
                    serviceModel.LastUpdatedAt = SqlUtil<DateTime>.GetValue(dr, "LastUpdatedAt");
                    serviceModel.LastUpdatedBy = SqlUtil<string>.GetValue(dr, "LastUpdatedBy");
                    serviceModel.StatusID = SqlUtil<int>.GetValue(dr, "StatusID");
                    serviceModel.ExtraFee = SqlUtil<float>.GetValue(dr, "ExtraFee");
                    serviceModel.ExtraFeeNote = SqlUtil<string>.GetValue(dr, "ExtraFeeNote");
                    serviceModel.BillingAddress = SqlUtil<string>.GetValue(dr, "BillingAddress");
                    serviceModel.ShippingAddress = SqlUtil<string>.GetValue(dr, "ShippingAddress");
                    serviceModel.CreatedBy = SqlUtil<string>.GetValue(dr, "CreatedBy");

                    orders.Add(serviceModel);
                }
            }

            return orders;
        }
    }
}
